﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Data;

namespace GA.Server.DatabaseManager
{
    public static class SqlConnectionExtensions
    {
        /// <summary>
        /// Executes a query on the database with no return value. Uses a custom query timeout to wait for a query to finish.
        /// </summary>
        /// <param name="query">String query to send to the database.</param>
        /// <param name="queryTimeout">Time (in seconds) to wait for a response from the server.</param>
        /// <exception cref="ArgumentNullException">Thrown if query is null.</exception>
        /// <exception cref="ArgumentException">Thrown if query is empty.</exception>
        /// <exception cref="SqlException">Thrown if the query is invalid, the server does not respond, the query times out, or you encounter a permissions error.</exception>
        public static void ExecuteNonQuery(this SqlConnection self, string query, int queryTimeout)
        {
            if (query == null) throw new ArgumentNullException("query");
            if (query.Length == 0) throw new ArgumentException("query cannot be empty", "query");

            self.Open();
            SqlCommand command = self.CreateCommand();
            command.CommandText = query;
            command.CommandTimeout = queryTimeout;
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();
            self.Close();
        }

        /// <summary>
        /// Executes a query on the database with a return <see cref="DataSet"/>. Uses a custom query timeout to wait for a query to finish.
        /// </summary>
        /// <param name="query">String query to send to the database.</param>
        /// <param name="queryTimeout">Time (in seconds) to wait for a response from the server.</param>
        /// <returns><see cref="DataSet"/> returned by the server.</returns>
        /// <exception cref="ArgumentNullException">Thrown if query is null.</exception>
        /// <exception cref="ArgumentException">Thrown if query is empty.</exception>
        /// <exception cref="SqlException">Thrown if the query is invalid, the server does not respond, the query times out, or you encounter a permissions error.</exception>
        public static DataSet Query(this SqlConnection self, string query, int queryTimeout)
        {
            if (query == null) throw new ArgumentNullException("query");
            if (query.Length == 0) throw new ArgumentException("query cannot be empty", "query");

            self.Open();
            SqlCommand command = self.CreateCommand();
            command.CommandText = query;
            command.CommandTimeout = queryTimeout;
            command.CommandType = CommandType.Text;
            DataSet dataSet = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet);
            self.Close();
            return dataSet;
        }

        /// <summary>
        /// Executes a stored procedure on the database with no return value. Uses a custom query timeout to wait for a query to finish.
        /// </summary>
        /// <param name="storedProcedureName">Exact name of the stored procedure to execute in the database.</param>
        /// <param name="parameters">Dictionary of parameters to send to the stored procedure.</param>
        /// <param name="queryTimeout">Time (in seconds) to wait for a response from the server.</param>
        /// <exception cref="ArgumentNullException">Thrown if query is null.</exception>
        /// <exception cref="ArgumentException">Thrown if query is empty.</exception>
        /// <exception cref="SqlException">Thrown if the query is invalid, the server does not respond, the query times out, or you encounter a permissions error.</exception>
        public static void ExecuteNonQueryStoredProcedure(this SqlConnection self, string storedProcedureName, Dictionary<string, object> parameters, int queryTimeout)
        {
            if (storedProcedureName == null) throw new ArgumentNullException("storedProcedureName");
            if (storedProcedureName.Length == 0) throw new ArgumentException("storedProcedureName cannot be empty.", "storedProcedureName");

            self.Open();
            SqlCommand command = self.CreateCommand();
            command.CommandText = storedProcedureName;
            command.CommandTimeout = queryTimeout;
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (string parameterKey in parameters.Keys)
                {
                    command.Parameters[parameterKey].Value = parameters[parameterKey];
                }
            }
            command.ExecuteNonQuery();
            self.Close();
        }

        /// <summary>
        /// Executes a stored procedure on the database with a return <see cref="DataSet"/>. Uses a custom query timeout to wait for a query to finish.
        /// </summary>
        /// <param name="storedProcedureName">Exact name of the stored procedure to execute in the database.</param>
        /// <param name="parameters">Dictionary of parameters to send to the stored procedure.</param>
        /// <param name="queryTimeout">Time (in seconds) to wait for a response from the server.</param>
        /// <returns><see cref="DataSet"/> returned by the server.</returns>
        /// <exception cref="ArgumentNullException">Thrown if query is null.</exception>
        /// <exception cref="ArgumentException">Thrown if query is empty.</exception>
        /// <exception cref="SqlException">Thrown if the query is invalid, the server does not respond, the query times out, or you encounter a permissions error.</exception>
        public static DataSet QueryStoredProcedure(this SqlConnection self, string storedProcedureName, Dictionary<string, object> parameters, int queryTimeout)
        {
            if (storedProcedureName == null) throw new ArgumentNullException("storedProcedureName");
            if (storedProcedureName.Length == 0) throw new ArgumentException("storedProcedureName cannot be empty.", "storedProcedureName");

            self.Open();
            SqlCommand command = self.CreateCommand();
            command.CommandText = storedProcedureName;
            command.CommandTimeout = queryTimeout;
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (string parameterKey in parameters.Keys)
                {
                    command.Parameters[parameterKey].Value = parameters[parameterKey];
                }
            }
            DataSet dataSet = new DataSet();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataSet);
            self.Close();
            return dataSet;
        }
    }
}
